
use -- the proper database below according to the current environment
    Events
 -- Test_Events
 -- Live_Events

GO
/****** Object:  StoredProcedure [dbo].[spGetIncidentsByCompanyIds2]    Script Date: 12/15/2017 5:51:03 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[spGetEventIndicatorsByCompanyIds2]
	@CompanyIds NVARCHAR(MAX), @timestamp DATETIME
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @XML XML
	SET @XML = CONVERT(XML, @CompanyIds, 1)

	DECLARE @SelectedCompanies TABLE
	(
		Id uniqueidentifier
	)
	INSERT INTO @SelectedCompanies
	SELECT TID.id.value('.','uniqueidentifier') AS Id
	FROM @XML.nodes('/CompanyIds') AS T(c) outer apply T.c.nodes('Id') as TID(id)

	SELECT	EventIndicators.Id AS EventIndicatorId,
			BaseEventIndicators.Number AS EventIndicatorCode, 
            BaseEventIndicators.Name AS EventIndicatorName, 
			BaseEventIndicators.SortKey AS EventIndicatorSortKey, 
			BaseEventIndicators.Id AS BaseEventIndicatorId,
			EventIndicatorCompanyProfiles.CompanyId, 
			BaseEventIndicators.ControversyIndicatorId,
			ControversyIndicators.Name AS ControversyIndicatorName,
			ControversyIndicators.Number AS ControversyIndicatorNumber,
			ControversyIndicators.SortKey AS ControversyIndicatorSortKey,
			EventIndicators.ProvidesControversy,
			IIF(AnswerCategories.Id > 2, EventIndicators.ControversySummary_Value, NULL) AS ControversySummary,
            AnswerCategories.Id AS AnswerCategoryId,
			AnswerCategories.Text AS AnswerCategoryText, 
			AnswerCategories.Score AS AnswerCategoryScore,
			IIF(AnswerCategories.Id > 2, EventIndicators.ACJustification_Value, NULL) AS AnswerCategoryJustification,
            EventIndicators.Impact_Value AS ImpactScore, 
			IIF(AnswerCategories.Id > 3, EventIndicators.ImpactText_Value, NULL) AS ImpactText,
			IIF(AnswerCategories.Id > 3, EventIndicators.ImpactCommentsId, NULL) AS ImpactCommentsId,

            EventIndicators.Risk_Value AS RiskScore, 
			IIF(AnswerCategories.Id > 3, EventIndicators.RiskText_Value, NULL) AS RiskText,
			IIF(AnswerCategories.Id > 3, EventIndicators.RiskCommentsId, NULL) AS RiskCommentsId,

            IIF(AnswerCategories.Id > 3, EventIndicators.ManagementAC_Value, NULL) AS PreparednessScore, 
			IIF(AnswerCategories.Id > 3, EventIndicators.ManagementText_Value, NULL) AS PreparednessText,
			IIF(AnswerCategories.Id > 3, EventIndicators.ManagementCommentsId, NULL) AS PreparednessCommentsId,
             
			IIF(AnswerCategories.Id > 2, EventIndicators.OutlookAC_Value, NULL) AS OutlookScore, 
			IIF(AnswerCategories.Id > 2, EventIndicators.OutlookText_Value, NULL) AS OutlookText, 
			IIF(AnswerCategories.Id > 2, EventIndicators.OutlookCommentsId, NULL) AS OutlookCommentsId,
			@timestamp AS Timestamp

	FROM	dbo.EventIndicatorCompanyProfiles AS EventIndicatorCompanyProfiles 
	INNER JOIN @SelectedCompanies AS SelectedCompanies ON SelectedCompanies.Id = EventIndicatorCompanyProfiles.CompanyId
    INNER JOIN dbo.EventIndicators AS EventIndicators ON EventIndicatorCompanyProfiles.Id = EventIndicators.CompanyProfileId 
    INNER JOIN dbo.BaseEventIndicators AS BaseEventIndicators ON EventIndicators.BaseEventIndicatorId = BaseEventIndicators.Id 
    INNER JOIN dbo.ControversyIndicators AS ControversyIndicators ON BaseEventIndicators.ControversyIndicatorId = ControversyIndicators.Id 
    INNER JOIN dbo.AnswerCategoryProperties AS AnswerCategoryProperties ON EventIndicators.AnswerCategoryPropertyId = AnswerCategoryProperties.Id 
	INNER JOIN dbo.AnswerCategories AS AnswerCategories ON AnswerCategoryProperties.ValueId = AnswerCategories.Id
	WHERE  (EventIndicatorCompanyProfiles.Status = 0 and BaseEventIndicators.UnavailableToClients = 0)
	ORDER BY EventIndicatorSortKey
END
GO



ALTER  PROCEDURE [dbo].[spGetEventIndicatorCommentsByCompanyIds2]
	@CompanyIds NVARCHAR(MAX), @timestamp DATETIME
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @XML XML
	SET @XML = CONVERT(XML, @CompanyIds, 1)

	DECLARE @SelectedCompanies TABLE
	(
		Id uniqueidentifier
	)
	INSERT INTO @SelectedCompanies
	SELECT TID.id.value('.','uniqueidentifier') AS Id
	FROM @XML.nodes('/CompanyIds') AS T(c) outer apply T.c.nodes('Id') as TID(id)

	SELECT	EventIndicatorCompanyProfiles.CompanyId,
			EventIndicators.Id AS EventIndicatorId, 
			EventIndicators.OutlookCommentsId AS CommentId, 
			OutlookComments.Text AS Comment, 
            OutlookComments.SortKey AS CommentSortKey,
			@timestamp AS Timestamp
	FROM	@SelectedCompanies AS SelectedCompanies
	INNER JOIN dbo.EventIndicatorCompanyProfiles AS EventIndicatorCompanyProfiles ON SelectedCompanies.Id = EventIndicatorCompanyProfiles.CompanyId AND EventIndicatorCompanyProfiles.Status = 0
	INNER JOIN dbo.EventIndicators AS EventIndicators ON EventIndicators.CompanyProfileId = EventIndicatorCompanyProfiles.Id 	
	INNER JOIN dbo.AnswerCategoryProperties AnswerCategoryProperties ON EventIndicators.AnswerCategoryPropertyId = AnswerCategoryProperties.Id AND AnswerCategoryProperties.ValueId > 2
	INNER JOIN dbo.BaseEventIndicators AS BaseEventIndicators ON EventIndicators.BaseEventIndicatorId = BaseEventIndicators.Id AND BaseEventIndicators.UnavailableToClients = 0
    INNER JOIN dbo.OutlookComments AS OutlookComments ON EventIndicators.OutlookCommentsId = OutlookComments.OutlookCommentsPropertyId

	UNION ALL 

	SELECT	EventIndicatorCompanyProfiles.CompanyId,
			EventIndicators.Id AS EventIndicatorId, 
			EventIndicators.ImpactCommentsId AS CommentId, 
			ImpactComments.Text AS Comment, 
            ImpactComments.SortKey AS CommentSortKey,
			@timestamp AS Timestamp
	FROM	@SelectedCompanies AS SelectedCompanies
	INNER JOIN dbo.EventIndicatorCompanyProfiles AS EventIndicatorCompanyProfiles ON SelectedCompanies.Id = EventIndicatorCompanyProfiles.CompanyId AND EventIndicatorCompanyProfiles.Status = 0
	INNER JOIN dbo.EventIndicators AS EventIndicators ON EventIndicators.CompanyProfileId = EventIndicatorCompanyProfiles.Id 
	INNER JOIN dbo.AnswerCategoryProperties AnswerCategoryProperties ON EventIndicators.AnswerCategoryPropertyId = AnswerCategoryProperties.Id AND AnswerCategoryProperties.ValueId > 3
	INNER JOIN dbo.BaseEventIndicators AS BaseEventIndicators ON EventIndicators.BaseEventIndicatorId = BaseEventIndicators.Id AND BaseEventIndicators.UnavailableToClients = 0
    INNER JOIN dbo.ImpactComments AS ImpactComments ON EventIndicators.ImpactCommentsId = ImpactComments.ImpactCommentsPropertyId

	UNION ALL 

	SELECT	EventIndicatorCompanyProfiles.CompanyId,
			EventIndicators.Id AS EventIndicatorId, 
			EventIndicators.RiskCommentsId AS CommentId, 
			RiskComments.Text AS Comment, 
            RiskComments.SortKey AS CommentSortKey,
			@timestamp AS Timestamp
	FROM	@SelectedCompanies AS SelectedCompanies
	INNER JOIN dbo.EventIndicatorCompanyProfiles AS EventIndicatorCompanyProfiles ON SelectedCompanies.Id = EventIndicatorCompanyProfiles.CompanyId AND EventIndicatorCompanyProfiles.Status = 0
	INNER JOIN dbo.EventIndicators AS EventIndicators ON EventIndicators.CompanyProfileId = EventIndicatorCompanyProfiles.Id 
	INNER JOIN dbo.AnswerCategoryProperties AnswerCategoryProperties ON EventIndicators.AnswerCategoryPropertyId = AnswerCategoryProperties.Id AND AnswerCategoryProperties.ValueId > 3
	INNER JOIN dbo.BaseEventIndicators AS BaseEventIndicators ON EventIndicators.BaseEventIndicatorId = BaseEventIndicators.Id AND BaseEventIndicators.UnavailableToClients = 0
    INNER JOIN dbo.RiskComments AS RiskComments ON EventIndicators.RiskCommentsId = RiskComments.RiskCommentsPropertyId

	UNION ALL 

	SELECT	EventIndicatorCompanyProfiles.CompanyId,
			EventIndicators.Id AS EventIndicatorId, 
			EventIndicators.ManagementCommentsId AS CommentId, 
			ManagementComments.Text AS Comment, 
            ManagementComments.SortKey AS CommentSortKey,
			@timestamp AS Timestamp
	FROM	@SelectedCompanies AS SelectedCompanies
	INNER JOIN dbo.EventIndicatorCompanyProfiles AS EventIndicatorCompanyProfiles ON SelectedCompanies.Id = EventIndicatorCompanyProfiles.CompanyId AND EventIndicatorCompanyProfiles.Status = 0
	INNER JOIN dbo.EventIndicators AS EventIndicators ON EventIndicators.CompanyProfileId = EventIndicatorCompanyProfiles.Id 
	INNER JOIN dbo.AnswerCategoryProperties AnswerCategoryProperties ON EventIndicators.AnswerCategoryPropertyId = AnswerCategoryProperties.Id AND AnswerCategoryProperties.ValueId > 3
	INNER JOIN dbo.BaseEventIndicators AS BaseEventIndicators ON EventIndicators.BaseEventIndicatorId = BaseEventIndicators.Id AND BaseEventIndicators.UnavailableToClients = 0
    INNER JOIN dbo.ManagementComments AS ManagementComments ON EventIndicators.ManagementCommentsId = ManagementComments.ManagementCommentsPropertyId

END
GO

